package data2db;

import cc.kave.commons.model.events.EventTrigger;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.time.Duration;
import java.time.ZonedDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicBoolean;

import static org.apache.mahout.common.parameters.Parametered.log;

public class PgUtils {
    private static AtomicBoolean initialized = new AtomicBoolean(false);
    public static void insertToPgDB(Object ce) {
        // 假设你已经建立了与数据库的连接
        try (Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/kavecc", "postgres", "123456")) {
            if( initialized.compareAndSet(false, true) )//如果为false，更新为true
            {
                // 创建查错表
                createTable(connection, UnSupportEventParam.class);
            }

            // 创建表
            createTable(connection, ce.getClass());
            // 插入数据
            insertData(connection, ce);

//            List<Map<String, String>>  dataList = getFiledsInfo(ce);
//            System.out.println("写入数据表 kavecc_table_" + ce.getClass().getSimpleName().toLowerCase() + "：");
//            for (Map<String, String> item : dataList) {
//                System.out.println("\t{");
//                for (Map.Entry<String, String> entry : item.entrySet()) {
//                    System.out.println("\t\t" + entry.getKey() + ": " + entry.getValue().toString());
//                }
//                System.out.println("\t}");
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }



    private static void createTable(Connection connection, Class<?> clazz) throws SQLException {
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS kavecc_table_" + clazz.getSimpleName().toLowerCase() + " (");
        Field[] fields = clazz.getFields();
        for (Field field : fields) {
            String fieldName = "kave_" + field.getName().toLowerCase();
            String fieldType = getSQLType(field.getType());
            sql.append(fieldName).append(" ").append(fieldType).append(",");
        }
        sql.deleteCharAt(sql.length() - 1); // Remove the last comma
        sql.append(")");

        try (Statement statement = connection.createStatement()) {
//            System.out.println("创建表数据表 kavecc_table_" + clazz.getSimpleName().toLowerCase() + " : " + sql);
            statement.executeUpdate(sql.toString());
//            System.out.println("创建数据表 kavecc_table_" + clazz.getSimpleName().toLowerCase() + "成功");
        } catch (Exception e) {
            System.out.println("创建数据表失败 kavecc_table_" + clazz.getSimpleName().toLowerCase() + " : " + e.toString());
        }
    }

    private static boolean canHandleType(Class<?> fieldType) {
        if (fieldType == String.class) {
            return true;
        } else if (fieldType == int.class || fieldType == Integer.class) {
            return true;
        } else if (fieldType == long.class || fieldType == Long.class) {
            return true;
        } else if (fieldType == float.class || fieldType == Float.class) {
            return true;
        } else if (fieldType == double.class || fieldType == Double.class) {
            return true;
        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
            return true;
        } else if (fieldType == java.time.Duration.class) {
            return true;
        } else if (fieldType == ZonedDateTime.class) {
//            return "TEXT";
            return true;
//            return "TIMESTAMP WITH TIME ZONE";
        } else if (fieldType == EventTrigger.class) {
            return true;
        } else {
            return false;
        }
    }

    private static String getSQLType(Class<?> fieldType) {
        if (fieldType == String.class) {
            return "TEXT";
        } else if (fieldType == int.class || fieldType == Integer.class) {
            return "INTEGER";
        } else if (fieldType == long.class || fieldType == Long.class) {
            return "BIGINT";
        } else if (fieldType == float.class || fieldType == Float.class) {
            return "REAL";
        } else if (fieldType == double.class || fieldType == Double.class) {
            return "DOUBLE PRECISION";
        } else if (fieldType == boolean.class || fieldType == Boolean.class) {
            return "BOOLEAN";
        } else if (fieldType == java.time.Duration.class) {
            return "TEXT";
        } else if (fieldType == ZonedDateTime.class) {
//            return "TEXT";
            return "TIMESTAMP";
//            return "TIMESTAMP WITH TIME ZONE";
        } else if (fieldType == EventTrigger.class) {
            return "TEXT";
        } else {
            return "TEXT";
        }
    }

    private static void insertData(Connection connection, Object clazz) throws SQLException {
        StringBuilder sql = new StringBuilder("INSERT INTO kavecc_table_" + clazz.getClass().getSimpleName().toLowerCase() + " (");
        StringBuilder values = new StringBuilder(" VALUES (");

        Field[] fields = clazz.getClass().getFields();
        boolean isNotNull = false;
        for (Field field : fields) {
            String fieldName = field.getName();
            Object value = getFieldValue(clazz, field);
            if (value != null) {
                sql.append("kave_" + fieldName.toLowerCase()).append(",");
                values.append("?,");
                isNotNull = true;
            }
        }
        if (!isNotNull) {
            System.out.println("取消写入数据表 kavecc_table_" + clazz.getClass().getSimpleName().toLowerCase());
            if (clazz.getClass() != UnSupportEventParam.class) {
                UnSupportEventParam us = new UnSupportEventParam();
                us.us_eventName = clazz.getClass().getName();
                us.us_paramType = "取消写入";
                us.us_paramValue = fields.toString();
                insertData(connection, us);
            }
            return;
        }
        sql.deleteCharAt(sql.length() - 1); // Remove the last comma
        values.deleteCharAt(values.length() - 1); // Remove the last comma
        sql.append(")").append(values).append(")");

//        System.out.println("写入数据表 kavecc_table_" + clazz.getClass().getSimpleName().toLowerCase() + ": " + sql);
        try (PreparedStatement statement = connection.prepareStatement(sql.toString())) {
            int parameterIndex = 1;
            for (Field field : fields) {
                Object value = getFieldValue(clazz, field);
                if (value != null) {
                    if (field.getType() == ZonedDateTime.class) {
                        value = java.sql.Timestamp.valueOf(((ZonedDateTime)value).toLocalDateTime());
                    } else if (field.getType().getSuperclass() == Enum.class
                            || field.getType() == Duration.class) {
                        value = String.valueOf(value);
                    }
                    if (!canHandleType(field.getType())) {
                        try {
                            value = String.valueOf(value);
                        } catch (Exception e1) {
                            value = "Can't toString()";
                        }
                        if (clazz.getClass() != UnSupportEventParam.class) {
                            UnSupportEventParam us = new UnSupportEventParam();
                            us.us_eventName = clazz.getClass().getName();
                            us.us_paramType = field.getType().getName();
                            us.us_paramValue = (String) value;
                            insertData(connection, us);
                        }
                    }
                    statement.setObject(parameterIndex++, value);
                }
            }
            statement.executeUpdate();
//            System.out.println("写入数据表 kavecc_table_" + clazz.getClass().getSimpleName().toLowerCase() + "成功");
        } catch (Exception e) {
            System.out.println("写入数据表失败 kavecc_table_" + clazz.getClass().getSimpleName().toLowerCase() + " : " + e.toString());
        }
    }

    private static Object getFieldValue(Object obj, Field field) {
        try {
            field.setAccessible(true);
            return field.get(obj);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static String[] getFiledName(Object o){
        Field[] fields=o.getClass().getFields();
        String[] fieldNames=new String[fields.length];
        for(int i=0;i<fields.length;i++){
//            System.out.println(fields[i].getType());
            fieldNames[i]=fields[i].getName();
        }
        return fieldNames;
    }

    /**
     * 获取属性类型(type)，属性名(name)，属性值(value)的map组成的list
     * */
    private static List getFiledsInfo(Object o){
//		Field[] fields=o.getClass().getDeclaredFields();
        Field[] fields=o.getClass().getFields();
        String[] fieldNames=new String[fields.length];
        List list = new ArrayList();
        Map infoMap=null;
        for(int i=0;i<fields.length;i++){
            infoMap = new HashMap();
            infoMap.put("type", fields[i].getType().toString());
            infoMap.put("name", fields[i].getName());
            infoMap.put("value", getFieldValueByName(fields[i].getName(), o));
            list.add(infoMap);
        }
        return list;
    }

    /**
     * 根据属性名获取属性值
     * */
    private static Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[] {});
            Object value = method.invoke(o, new Object[] {});
            return value;
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
    }

    /**
     * 获取对象的所有属性值，返回一个对象数组
     * */
    private static Object[] getFiledValues(Object o){
        String[] fieldNames = getFiledName(o);
        Object[] value=new Object[fieldNames.length];
        for(int i=0;i<fieldNames.length;i++){
            value[i] = getFieldValueByName(fieldNames[i], o);
        }
        return value;
    }

}
